/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Program;
import com.tces.model.Student;
import com.tces.model.User;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author arnolda
 */
public class UserDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(UserDAO.class);

    public static List<User> findAll() {
        List<User> userList = new ArrayList<User>();
        try {

            String sql = "SELECT\n"
                    + "u.user_id,\n"
                    + "u.user_first_name,\n"
                    + "u.user_middle_name,\n"
                    + "u.user_last_name,\n"
                    + "u.user_password,\n"
                    + "u.user_visible,\n"
                    + "u.user_idno,\n"
                    + "c.college_id,\n"
                    + "c.college_code,\n"
                    + "c.college_name,\n"
                    + "r.role_id,\n"
                    + "r.role_name,\n"
                    + "r.role_description\n"
                    + "FROM\n"
                    + "users AS u\n"
                    + "INNER JOIN colleges AS c ON c.college_id = u.college_id\n"
                    + "INNER JOIN roles AS r ON r.role_id = u.role_id";

            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            //        preparedStatement.setInt(1, subjectOfferingID);
            log.info(preparedStatement.toString());
            ResultSet rs = preparedStatement.executeQuery();
            User user;
            while (rs.next()) {
                user = new User();

                user.setCollegeID(rs.getInt("college_id"));
                user.setCollegeCode(rs.getString("college_code"));
                user.setCollegeName(rs.getString("college_name"));

                user.setRoleID(rs.getInt("role_id"));
                user.setRoleName(rs.getString("role_name"));
                user.setRoleDescription(rs.getString("role_description"));

                user.setUserID(rs.getInt("user_id"));
                user.setUserIDNumber(rs.getString("user_idno"));
                user.setUserFirstName(rs.getString("user_first_name"));
                user.setUserMiddleName(rs.getString("user_middle_name"));
                user.setUserLastName(rs.getString("user_last_name"));
                user.setUserVisible(rs.getInt("user_visible"));

                userList.add(user);
            }
            closeConnection(getConnectionObject());

        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        return userList;
    }

    public static User authenticate(String userIDNumber, String userPassword) throws Exception {
        User user = null;
        String sql = "SELECT\n"
                + "u.user_id,\n"
                + "u.user_idno,\n"
                + "u.user_first_name,\n"
                + "u.user_middle_name,\n"
                + "u.user_last_name,\n"
                + "u.user_password,\n"
                + "u.user_visible,\n"
                + "r.role_id,\n"
                + "r.role_name,\n"
                + "r.role_description,\n"
                + "c.college_id,\n"
                + "c.college_code,\n"
                + "c.college_name\n"
                + "FROM\n"
                + "users AS u\n"
                + "INNER JOIN roles AS r ON r.role_id = u.role_id\n"
                + "INNER JOIN colleges AS c ON c.college_id = u.college_id\n"
                + "WHERE u.user_visible=1\n"
                + "AND u.user_idno=?\n"
                + "AND u.user_password=md5(?)";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setString(1, userIDNumber);
        preparedStatement.setString(2, userPassword);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();

        while (rs.next()) {
            user = new User();
            user.setUserID(rs.getInt("user_id"));
            user.setRoleID(rs.getInt("role_id"));
            user.setCollegeID(rs.getInt("college_id"));
            user.setUserVisible(rs.getInt("user_visible"));
            user.setCollegeCode(rs.getString("college_code"));
            user.setCollegeName(rs.getString("college_name"));
            user.setRoleName(rs.getString("role_name"));
            user.setRoleDescription(rs.getString("role_description"));
            user.setUserIDNumber(rs.getString("user_idno"));
            user.setUserFirstName(rs.getString("user_first_name"));
            user.setUserMiddleName(rs.getString("user_middle_name"));
            user.setUserLastName(rs.getString("user_last_name"));
        }
        closeConnection(getConnectionObject());

        return user;
    }

    public static int savePassword(String userIDNumber, String userPassword) throws Exception {
        int result = 0;
        try {
            String sql = "UPDATE users SET user_password = MD5(?) WHERE user_idno = ?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, userPassword);
            preparedStatement.setString(2, userIDNumber);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        return result;
    }

    public static int add(User user) {
        int result = 0;
        try {
            String sql = "INSERT INTO users(user_idno,user_first_name,user_middle_name,user_last_name,user_password, user_visible,role_id,college_id) values(?,?,?,?,md5(?),?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, user.getUserIDNumber());
            preparedStatement.setString(2, user.getUserFirstName());
            preparedStatement.setString(3, user.getUserMiddleName());
            preparedStatement.setString(4, user.getUserLastName());
            preparedStatement.setString(5, user.getUserIDNumber());
            preparedStatement.setInt(6, user.getUserVisible());
            preparedStatement.setInt(7, user.getRoleID());
            preparedStatement.setInt(8, user.getCollegeID());
            log.info(preparedStatement.toString());
            result = preparedStatement.executeUpdate();
            closeConnection(getConnectionObject());
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        return result;
    }

    public static int update(User user) {
        int result = 0;
        try {
            String sql = "UPDATE users SET user_idno=?,user_first_name=?,user_middle_name=?,user_last_name=?,user_visible=?,role_id=?,college_id=? WHERE user_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, user.getUserIDNumber());
            preparedStatement.setString(2, user.getUserFirstName());
            preparedStatement.setString(3, user.getUserMiddleName());
            preparedStatement.setString(4, user.getUserLastName());
            preparedStatement.setInt(5, user.getUserVisible());
            preparedStatement.setInt(6, user.getRoleID());
            preparedStatement.setInt(7, user.getCollegeID());
            preparedStatement.setInt(8, user.getUserID());
            log.info(preparedStatement.toString());
            result = preparedStatement.executeUpdate();
            closeConnection(getConnectionObject());
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        return result;
    }
}
